CREATE DATABASE QUANLYRAPCHIEUPHIM_DOAN_PTUDCSDL1
GO
USE QUANLYRAPCHIEUPHIM_DOAN_PTUDCSDL1
GO
CREATE TABLE BANGGIA
(
	MAGIAVE VARCHAR(10) NOT NULL,
	GIAVE FLOAT,
	MADINHDANGPHIM INT,
	MALOAIKH  INT,
	MASUATCHIEU VARCHAR(8),
	CONSTRAINT PK_BANGGIA PRIMARY KEY (MAGIAVE)
)
GO
CREATE TABLE TINHTRANG
(
	MATINHTRANG VARCHAR(10),
	TENTINHTRANG NVARCHAR(20),
	CONSTRAINT PK_TINHTRANG PRIMARY KEY (MATINHTRANG)
)
GO
CREATE TABLE KHACHHANG
(
	ID INT IDENTITY (1,1) NOT NULL,
	MAKH AS 'KH' + RIGHT('0000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,
	TENKH NVARCHAR(20),
	MALOAIKH INT,
	MALOAIND INT,
	MAUSER VARCHAR(5),
	NGAYSINH DATE,
	EMAIL NVARCHAR(20),
	THETHANHVIEN BIT,
	CONSTRAINT PK_KHACHHANG PRIMARY KEY (MAKH)
)
GO
CREATE TABLE NHANVIEN
(
	MANHANVIEN VARCHAR(10),
	TENNHANVIEN NVARCHAR(20),
	MALOAIND INT,
	MAUSER VARCHAR (5),
	CONSTRAINT PK_NHANVIEN PRIMARY KEY (MANHANVIEN)
)
GO
CREATE TABLE NGUOIDUNG
(
	ID INT IDENTITY (1,1) NOT NULL,
	MAUSER AS 'US' + RIGHT('0000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,
	USERNAME VARCHAR(20),
	PASS VARCHAR(20),
	--MAPHANQUYEN INT,
	MALOAIND INT,
	CONSTRAINT PK_NGUOIDUNG PRIMARY KEY (MAUSER)
)
GO
--CREATE TABLE PHANQUYEN
--(
--	MAPHANQUYEN INT NOT NULL,
--	TENPHANQUYEN NVARCHAR (20),
--	CONSTRAINT PK_PHANQUYEN PRIMARY KEY (MAPHANQUYEN)
--)
--GO
CREATE TABLE LOAINGUOIDUNG
(
	MALOAIND INT NOT NULL,
	TENLOAIND VARCHAR(20),
	CONSTRAINT PK_LOAINGUOIDUNG PRIMARY KEY (MALOAIND)
)
GO
CREATE TABLE HOADON
(
	ID INT IDENTITY (1,1) NOT NULL,
	MAHD AS 'HD' + RIGHT('0000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,
	GIOLAP DATETIME,
	TONGTIEN FLOAT,
	MANHANVIEN VARCHAR (10),
	MAKH VARCHAR(5),
	CONSTRAINT PK_HOADON PRIMARY KEY (MAHD)
)
GO
CREATE TABLE CT_HD
(
	STT INT IDENTITY (1,1) NOT NULL,
	MAHD VARCHAR(10) NOT NULL,
	MAGIAVE VARCHAR(10) NOT NULL,
	MASUATCHIEU VARCHAR(8),
	MARAP VARCHAR(7),
	MAPHONG VARCHAR (8),
	MAPHIM VARCHAR (7),
	VITRIGHE VARCHAR(10),
	GIATIEN FLOAT,
	SOLUONG INT,
	MUCGIAM INT,
	THANHTIEN FLOAT,
	MATINHTRANGGHE INT,
	CONSTRAINT PK_CT_HD PRIMARY KEY (STT, MAHD, MAGIAVE)
)
GO
CREATE TABLE TINHTRANGGHE
(
	MATINHTRANGGHE INT NOT NULL,
	TENTINHTRANGGHE NVARCHAR(20),
	CONSTRAINT PK_TINHTRANGGHE PRIMARY KEY (MATINHTRANGGHE)
)
GO
CREATE TABLE Phim
(
	id int  IDENTITY (1,1) NOT NULL,
	MaPhim AS 'P' + RIGHT('0000' + CAST(ID AS varCHAR(6)), 6) PERSISTED,
	TenPhim nvarchar(100),
	GioiThieuPhim nvarchar(max),
	ThoiLuongPhim nvarchar(50),
	NamSX char(10),
	NuocSX nvarchar(50),
	HangSX nvarchar(50),
	DSDienVien nvarchar(max),
	Poster text,
	Trailer text,
	BinhChonPhim int,
	MaDinhDangPhim int,
	CONSTRAINT pk_phim PRIMARY KEY (MaPhim)
)
GO
CREATE TABLE DinhDangPhim
(
	MaDinhDangPhim int NOT NULL,
	TenDinhDangPhim varchar(10),
	CONSTRAINT pk_DinhDangPhim PRIMARY KEY (MaDinhDangPhim)
)
GO
CREATE TABLE RapChieu
(
	id int IDENTITY (1,1) NOT NULL,
	MaRap AS 'R' + RIGHT('0000' + CAST(ID AS varCHAR(6)), 6) PERSISTED,
	TenRap nvarchar(50) ,
	DiaChi nvarchar(max) ,
	CONSTRAINT pk_RapChieu PRIMARY KEY (MaRap)
)
GO
CREATE TABLE DangKyPhim
(
	id int IDENTITY (1,1) NOT NULL,	
	MaDKPhim AS 'DK' + RIGHT('0000' + CAST(ID AS varCHAR(6)), 6) PERSISTED,
	MaPhim varchar(7) NOT NULL,
	MARAP VARCHAR(7) NOT NULL,
	CONSTRAINT pk_DangKyPhim PRIMARY KEY (MaDKPhim)
)
GO
CREATE TABLE PhongChieu
(
	id int IDENTITY (1,1) NOT NULL,	
	MaPhongChieu AS 'PC' + RIGHT('0000' + CAST(ID AS varCHAR(6)), 6) PERSISTED,
	TenPhong nvarchar(20) ,
	SoHangGhe int ,
	SoDayGhe int ,
	MoTaPhongChieu nvarchar(max) ,
	MaRap char(10),
	MaTinhTrangPhong int,
	DieuKienKyThuat int,
	CONSTRAINT pk_PhongChieu PRIMARY KEY (MaPhongChieu)
)
GO
CREATE TABLE DangKySuatChieu
(
	id int IDENTITY (1,1) NOT NULL,	
	MaDK  AS 'SC' + RIGHT('0000' + CAST(ID AS varCHAR(6)), 6) PERSISTED,
	MaPhim varchar(7) NOT NULL,
	MaPhongChieu varchar(8) NOT NULL,
	MaSuatChieu varchar(8) NOT NULL,
	CONSTRAINT pk_DangKySuatChieu PRIMARY KEY (MaDK, MaPhim, MaPhongChieu)
)
GO
CREATE TABLE SuatChieu
(
	id int IDENTITY (1,1) NOT NULL,	
	MaSuatChieu  AS 'SC' + RIGHT('0000' + CAST(ID AS varCHAR(6)), 6) PERSISTED,
	GioBatDau int, 
	GioKetThuc int
	CONSTRAINT pk_SuatChieu PRIMARY KEY (MaSuatChieu)
)
GO
CREATE TABLE TheLoaiPhim
(
	MaTheLoaiPhim int NOT NULL,
	TenTheLoaiPhim nvarchar(50) ,
	CONSTRAINT pk_TheLoaiPhim PRIMARY KEY (MaTheLoaiPhim)
)
GO
CREATE TABLE TheLoai_Phim
(
	STT int,
	MaTheLoaiPhim int NOT NULL,
	MaPhim varchar(7)NOT NULL,
	CONSTRAINT pk_TheLoai_Phim PRIMARY KEY (MaTheLoaiPhim, MaPhim)
)
GO
CREATE TABLE KhachHang_Phim
(
	STT int NOT NULL,
	MaPhim varchar(7) NOT NULL,
	MaLoaiKH int,
	CONSTRAINT pk_KhachHang_Phim PRIMARY KEY (STT, MaPhim,MaLoaiKH)
)
GO
CREATE TABLE LoaiKhachHang
(
	MaLoaiKhachHang int NOT NULL,
	TenLoaiKhachHang char(10),
	CONSTRAINT pk_LoaiKhachHang PRIMARY KEY (MaLoaiKhachHang)
)
GO
CREATE TABLE TinhTrangPhong
(
	MaTinhTrangPhong int NOT NULL,
	TenTinhTrangPhong nvarchar(10) ,
	CONSTRAINT pk_TinhTrangPhong PRIMARY KEY (MaTinhTrangPhong)
)
GO
alter table CT_HD add constraint fk_CT_HD_TINHTRANGGHE  foreign key (MATINHTRANGGHE) references TINHTRANGGHE(MATINHTRANGGHE)
alter table CT_HD add constraint fk_CT_HD_SuatChieu  foreign key (MASUATCHIEU) references SuatChieu(MaSuatChieu)
alter table CT_HD add constraint fk_CT_HD_RapChieu  foreign key (MARAP) references RapChieu (MaRap)
alter table CT_HD add constraint fk_CT_HD_PhongChieu  foreign key (MAPHONG) references PhongChieu (MaPhongChieu)
alter table CT_HD add constraint fk_CT_HD_Phim foreign key (MAPHIM) references Phim (MaPhim)
alter table HOADON add constraint fk_HOADON_NHANVIEN  foreign key (MANHANVIEN) references NHANVIEN(MANHANVIEN)
alter table HOADON add constraint fk_HOADON_KHACHHANG  foreign key (MAKH) references KHACHHANG(MAKH)
--alter table NGUOIDUNG add constraint fk_NGUOIDUNG_PHANQUYEN foreign key (MAPHANQUYEN) references PHANQUYEN(MAPHANQUYEN)
alter table NGUOIDUNG add constraint fk_NGUOIDUNG_LOAINGUOIDUNG foreign key (MALOAIND) references LOAINGUOIDUNG(MALOAIND)
alter table KHACHHANG add constraint fk_KHACHHANG_LoaiKhachHang foreign key (MALOAIKH) references LoaiKhachHang(MaLoaiKhachHang)
ALTER TABLE KHACHHANG ADD CONSTRAINT FK_KHACHHANG_NGUOIDUNG FOREIGN KEY (MAUSER) REFERENCES NGUOIDUNG (MAUSER)
ALTER TABLE KHACHHANG ADD CONSTRAINT FK_KHACHHANG_LOAINGUOIDUNG FOREIGN KEY (MALOAIND) REFERENCES LOAINGUOIDUNG (MALOAIND)
ALTER TABLE NHANVIEN ADD CONSTRAINT FK_NHANVIEN_LOAINGUOIDUNG FOREIGN KEY (MALOAIND) REFERENCES LOAINGUOIDUNG (MALOAIND)
ALTER TABLE NHANVIEN ADD CONSTRAINT FK_NHANVIEN_NGUOIDUNG FOREIGN KEY (MAUSER) REFERENCES NGUOIDUNG (MAUSER)
alter table BANGGIA add constraint fk_BANGIA_DinhDangPhim foreign key (MADINHDANGPHIM) references DinhDangPhim(MaDinhDangPhim)
alter table BANGGIA add constraint fk_BANGIA_LOAIKHACHHANG foreign key (MALOAIKH) references LOAIKHACHHANG(MaLoaiKhachHang)
alter table BANGGIA add constraint fk_BANGIA_SUATCHIEU foreign key (MASUATCHIEU) references SuatChieu(MaSuatChieu)


alter table Phim add constraint fk_Phim_DinhDangPhim  foreign key (MaDinhDangPhim) references DinhDangPhim(MaDinhDangPhim)
alter table PhongChieu add constraint fk_PhongChieu_DinhDangPhim  foreign key (DieuKienKyThuat) references DinhDangPhim(MaDinhDangPhim)
alter table PhongChieu add constraint fk_PhongChieu_TinhTrangPhong  foreign key (MaTinhTrangPhong) references TinhTrangPhong(MaTinhTrangPhong)

alter table DangKySuatChieu add constraint fk_DangKySuatChieuu_Phim  foreign key (MaPhim) references Phim(MaPhim)
alter table DangKySuatChieu add constraint fk_DangKySuatChieu_PhongChieu  foreign key (MaPhongChieu) references PhongChieu(MaPhongChieu)
alter table DangKySuatChieu add constraint fk_DangKySuatChieu_SuatChieu  foreign key (MaSuatChieu) references SuatChieu(MaSuatChieu)

alter table DangKyPhim add constraint fk_DangKyPhim_Phim  foreign key (MaPhim) references Phim(MaPhim)
alter table DangKyPhim add constraint fk_DangKyPhim_RapChieu  foreign key (MaRap) references RapChieu(MaRap)

alter table TheLoai_Phim add constraint fk_TheLoaiPhim_Phim  foreign key (MaPhim) references Phim(MaPhim)
alter table TheLoai_Phim add constraint fk_TheLoaiPhim_LoaiPhim  foreign key (MaTheLoaiPhim) references TheLoaiPhim(MaTheLoaiPhim)

alter table KhachHang_Phim add constraint fk_KhachHangPhim_LoaiKhachHang  foreign key (MaLoaiKH) references LoaiKhachHang(MaLoaiKhachHang)
alter table KhachHang_Phim add constraint fk_KhachHangPhim_Phim  foreign key (MaPhim) references Phim(MaPhim)


--DROP DATABASE QUANLYRAPCHIEUPHIM_DOAN_PTUDCSDL1

